ICTSC Tech Blog

ICTSC2019 一次予選 問題解説: ストアドプロシージャってやつでなんとかして！！！！

ICTSC2019 一次予選 問題解説

問題文

あなたはA社に今年入社した新入社員です。
A社は人手不足でAさんの他に上司のBさんしか情報システム部門にいません。
ある日突然Bさんが人事のCさんに、「簡単に指定のデータを取り出せるようにしてほしい」と言われました。
ですが、Bさんは次の日からバカンスに行く予定があったため、あなたに任せて出国してしまいました。
Bさんの代わりにCさんを助けてあげてください。

問1(q01)

  • 性別名が”男”の従業員のみを取得してください。
  • カラムは(employee_id, name, birthday, sex_name)を表示してください。

問2(q02)

  • 性別名が”女”で年齢が女性従業員の平均年齢以上の従業員のみを取得してください。
  • カラムは(employee_id, name, age, sex_name)を表示してください。
  • age(年齢)に関してはbirthdayから求めて答えてください。

問3(q03)

  • 部署も権限もすでに決まっている(NULL以外である)従業員のデータのみを取得してください。
  • カラムは(employee_id,name,age,department_name,permit_name)
  • age(年齢)に関してはbirthdayから求めて答えてください。

条件

Employees以外のテーブル内にあるカラム”〇〇_id”は今後内容が変更されることがあるので注意すること。

ゴール

問1,2,3で指定したデータを取得するクエリを、q01,q02,q03という名前でストアドプロシージャとして登録する。

情報

問題サーバー

  • IPアドレス: 192.168.0.1
  • ユーザー: admin
  • パスワード: 6pfmqje365Ed
  • DBユーザー: root
  • DBパスワード: 無し

解説・解答例

解説

　この問題は指定されたデータのみをストアドプロシージャを利用してCall (小問id)で簡単に呼び出せるようにしてあげるというものでした。
なので、最初に必要なデータを取得するためのクエリを用意しないといけません。
用意する際は以下の点に気をつけなければいけません。

  • 問1(q01)
    • 性別名が男なので、sexテーブルのsex_nameが男と一致してなければいけない
      • JOIN句を用いてsexテーブルとEmployeesテーブルを結合したあとにWhere句を用いてsex_nameが男のレコードのみを抽出する。
  • 問2(q02)
    • ageを求めるためにbirthdayから変換しないといけない
      • 現在の日付と誕生日の差を取り、その年数を求めることで年齢を求められる
  • 問3(q03)
    • q02と同様にageをbirthdayから求めないといけない
    • Join句を2回用いていなければいけない。
      • 順番はDepartmentsテーブルからでもPermitテーブルからのどちらからでも問題ない
  • 共通
    • クエリの内容でWHERE句とHAVING句を混同するなど動作が未定義な用法をしている場合は減点対象になっている

ここまでで用意したクエリをそれぞれプロシージャとして保存しなければいけませんでした。
クエリの最初にCREATE PROCEDURE [小問id]()をつけて実行することでそのクエリを保存することができます。
ここまでの作業を行ったうえでCall [小問id]();を実行することで簡単に指定したデータを取り出せるようになります。

解答例

問1(q01)

CREATE PROCEDURE q01() SELECT employee_id, name, birthday, sex_name FROM Employees LEFT OUTER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name = "男";

問2(q02)

CREATE PROCEDURE q02() SELECT employee_id, name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age, sex_name FROM Employees LEFT OUTER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name= "女" AND TIMESTAMPDIFF(YEAR, birthday, CURDATE())>= (SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) FROM Employees LEFT OUTER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name="女");

問3(q03)

CREATE PROCEDURE q03() SELECT employee_id,name,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age,department_name,permit_name FROM Employees AS Emp JOIN Departments AS Dep ON Emp.department_id=Dep.department_id JOIN Permit AS Per ON Emp.permit_id=Per.permit_id;

講評

　今回は一次予選で最近出題が少なかったデータベースの問題ということで、どちらかというとオペレーションの簡略化とミス防止、ストアド・プロシージャという機能を知っているかということを意識して作問しました。
　問1～3まで通して、ストアドプロシージャという機能を使えるかということを中心に、問題文の条件にも書いてあるようにテーブル内のデータの増減や変更などに耐えられるクエリがかけているかということを中心に採点を行いました。
　今回の解答を見ている中で惜しいミスがいくつかあり、例えばsex_id=0など、Employeesテーブル以外のsex_idとsex_nameが今後変化することがあるかもしれないのに抽出条件に書いてしまっている解答が多く見られました。
また、解答では正答を書いているにも関わらず問題環境でオペレーションを行っていないチームが多数ありました。
こちらについては、問題の本質について理解しているということで各小問50%減点で採点をさせていただきました。
減点の理由としては問題の本質について理解していても、オペレーションの時間を他の問題に費やせていたかもしれないという考えからです。
　今回、採点をした感想としては上位層はかなり高い割合で問題を完答しており、DBの基本的なところがしっかり理解していそうだなと感じました。
今回出題した機能は、SQLの入門書の最後の方に出てくるかどうかという機能でした。
ストアドプロシージャ以外にもMySQLやMariaDBには様々な機能が備わっているのでぜひ調べて見てください！